package com.campusbackend.mapper;

import com.campusbackend.entity.StudentSkills;
import com.campusbackend.entity.vo.StudentSkillVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * <p>
 * 学生技能关联表 Mapper 接口
 * </p>
 *
 * @author yezi
 * @since 2025-07-24
 */
@Mapper
public interface StudentSkillsMapper extends BaseMapper<StudentSkills> {

    /**
     * 批量插入学生技能关联
     * @param studentId 学生ID
     * @param skillIds 技能ID列表
     * @return 插入的记录数
     */
    @Insert({
        "<script>",
        "INSERT INTO student_skills (student_id, skill_id, proficiency_level, create_time) VALUES ",
        "<foreach collection='skillIds' item='skillId' separator=','>",
        "(#{studentId}, #{skillId}, 1, NOW())",
        "</foreach>",
        "</script>"
    })
    int batchInsertStudentSkills(@Param("studentId") Long studentId, @Param("skillIds") List<Long> skillIds);

    /**
     * 删除学生的所有技能关联
     * @param studentId 学生ID
     * @return 删除的记录数
     */
    @Delete("DELETE FROM student_skills WHERE student_id = #{studentId}")
    int deleteByStudentId(@Param("studentId") Long studentId);

    /**
     * 获取学生的技能列表
     * @param studentId 学生ID
     * @return 学生技能列表
     */
    @Select("SELECT ss.*, s.skill_name, s.skill_category, s.description " +
            "FROM student_skills ss " +
            "LEFT JOIN skills s ON ss.skill_id = s.id " +
            "WHERE ss.student_id = #{studentId} " +
            "ORDER BY s.skill_category, s.skill_name")
    List<StudentSkills> getStudentSkills(@Param("studentId") Long studentId);

    /**
     * 获取学生的技能名称列表
     * @param studentId 学生ID
     * @return 技能名称列表
     */
    @Select("SELECT s.skill_name " +
            "FROM student_skills ss " +
            "LEFT JOIN skills s ON ss.skill_id = s.id " +
            "WHERE ss.student_id = #{studentId} AND s.skill_name IS NOT NULL " +
            "ORDER BY s.skill_category, s.skill_name")
    List<String> getStudentSkillNames(@Param("studentId") Long studentId);

    /**
     * 获取学生基本信息
     * @param studentId 学生ID
     * @return 学生基本信息
     */
    @Select("SELECT " +
            "id as student_id, " +
            "real_name as student_name, " +
            "student_id as student_number, " +
            "college " +
            "FROM students " +
            "WHERE id = #{studentId}")
    StudentSkillVO getStudentBasicInfo(@Param("studentId") Long studentId);

    /**
     * 获取学生的详细技能信息
     * @param studentId 学生ID
     * @return 学生技能详细信息列表
     */
    @Select("SELECT " +
            "ss.skill_id, " +
            "s.skill_name, " +
            "s.skill_category, " +
            "s.description, " +
            "ss.proficiency_level, " +
            "ss.create_time " +
            "FROM student_skills ss " +
            "LEFT JOIN skills s ON ss.skill_id = s.id " +
            "WHERE ss.student_id = #{studentId} AND s.status = 1 " +
            "ORDER BY s.skill_category, s.skill_name")
    List<StudentSkillVO.SkillInfo> getStudentSkillDetails(@Param("studentId") Long studentId);
}
